MYSQL VIEWS

The VIEW is the virtual table that can combines the columns of multiple tables and returns the records. It doesnt hold the actual table data.

With the help of VIEW we can hide the complex query behavior from others.

CREATE VIEW

Using the CREATE VIEW statement it is possible to create a new view.

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

CREATE VIEW [Indian Employees] AS
select * from employees
where location = 'India';

To query the above view with the name [Indian Employees], we use the following query

select * from [Indian Employees];

This will return the records of the employees whose location is India.

CREATE VIEW [Ordered Customers] AS
    select customer.name from 
    customer
    inner join orders
    on customer.cust_id=orders.cust_id;

Query the [Ordered Customers] view with the following query

SELECT * FROM [Ordered Customers];

Its possible to update an existing VIEW using the command CREATE OR REPLACE VIEW.

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

CREATE VIEW [Indian Employees] AS 
select name,location from employees 
where location = 'India';

The above query will update [Indian Employees] view to return the name and location of Indian employees.

To delete an existing view, use the DROP VIEW command.

DROP  VIEW  view_name;

DROP VIEW [Indian Employees];